from dao.BaseDao import BaseDao
from Static import Static
import pandas as pd
from util import CommonHelper


class ReferDao(BaseDao):
    def initDB(self):
        self.__createTableRefer()

    def __createTableRefer(self):
        sql = """
            CREATE TABLE IF NOT EXISTS `{}` (
                `id` int NOT NULL AUTO_INCREMENT,
                `patient_id` int,
                `out_dept` int,
                `in_dept` int,
                `doctor_id` int,
                `status` int,
                `create_date` date,
                `diagnosis` VARCHAR ( 255 ) DEFAULT '-',
                `according` VARCHAR ( 255 ) DEFAULT '-',
            PRIMARY KEY ( `id` ) 
            ) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;""".format(
            Static.DB_Refer
        )
        self.executeNoResult(sql)

    def getReferList(self):
        self.__createTableRefer()
        sql_query = """
        SELECT
            r.`id`,
            r.`patient_id` patientId,
            r.`create_date` createDate,
            u.`nickname` doctorName,
            r.`in_dept` inDeptId,
            din.`name` inDoctName,
            r.`out_dept` outDeptId,
            dout.`name` outDoctName,
            r.`status`
        FROM
            `{}` r
            LEFT JOIN `{}` din ON din.id = r.in_dept
            LEFT JOIN `{}` dout ON dout.id = r.out_dept
            LEFT JOIN `{}` u ON u.id = r.doctor_id""".format(
            Static.DB_Refer, Static.DB_Department, Static.DB_Department, Static.DB_User
        )
        df = self.readSQL(sql_query)
        return df

    def getReferInfo(self, referId):
        self.__createTableRefer()
        sql_query = """
            SELECT * FROM `{}` WHERE `id` = {}
        """.format(
            Static.DB_Refer, referId
        )
        df = self.readSQL(sql_query)
        return df

    def checkReferExist(self, patientId, createDate):
        self.__createTableRefer()
        sql_query = """
            SELECT `id` FROM `{}` WHERE `patient_id` = {} AND `create_date` = '{}'
        """.format(
            Static.DB_Refer, patientId, createDate
        )
        df = self.readSQL(sql_query)
        return df.shape[0] > 0

    def checkReferExistByReferId(self, referId):
        self.__createTableRefer()
        sql_query = """
            SELECT `id` FROM `{}` WHERE `id` = {} AND `status` = 0
        """.format(
            Static.DB_Refer, referId
        )
        df = self.readSQL(sql_query)
        return df.shape[0] > 0

    def addRefers(self, df):
        self.__createTableRefer()
        self.write2sql(df, Static.DB_Refer)

    def updateRefer(
        self,
        referId,
        patientId,
        outDeptId,
        inDeptId,
        doctorId,
        createDate,
        diagnosis,
        according,
    ):
        self.__createTableRefer()
        sql_query = """
            UPDATE `{}`
            SET
                `patient_id` = {},
                `out_dept` = {},
                `in_dept` = {},
                `doctor_id` = {},
                `create_date` = '{}',
                `diagnosis` = '{}',
                `according` = '{}'
            WHERE
                `id` = {}
        """.format(
            Static.DB_Refer,
            patientId,
            outDeptId,
            inDeptId,
            doctorId,
            createDate,
            diagnosis,
            according,
            referId,
        )
        self.executeNoResult(sql_query)

    def delRefer(self, referId):
        self.__createTableRefer()
        sql_query = """
            DELETE FROM `{}` WHERE `id` = {}
        """.format(
            Static.DB_Refer, referId
        )
        self.executeNoResult(sql_query)

    def checkTodoRefer(self, deptId):
        self.__createTableRefer()
        sql_query = """
            SELECT
                r.`id`,
                r.`status`,
                r.create_date createDate,
                d.`name` outDeptName,
                p.`name` patientName,
                p.genderStr patientGender 
            FROM
                `{}` r
                LEFT JOIN `{}` d ON d.`id` = r.out_dept
                LEFT JOIN (
                SELECT
                    pp.`id`,
                    pp.gender,
                    pp.`name`,
                    s.dictLabel genderStr 
                FROM
                    `{}` pp
                    LEFT JOIN ( SELECT * FROM `{}` WHERE `key` = 'sys_user_sex' ) s ON s.dictValue = pp.gender 
                ) p ON p.`id` = r.patient_id 
            WHERE
                r.`in_dept` = '{}' 
                AND r.`status` = 0""".format(
            Static.DB_Refer,
            Static.DB_Department,
            Static.DB_Patient,
            Static.DB_SystemDict,
            deptId,
        )
        print(sql_query)
        df = self.readSQL(sql_query)
        return df

    def confirmRefer(self, referId):
        self.__createTableRefer()
        sql_query = """
            UPDATE `{}`
            SET
                `status` = 1
            WHERE
                `id` = {}
        """.format(
            Static.DB_Refer, referId
        )
        self.executeNoResult(sql_query)
